// CDN_DB_Sqlite.h
//
// Copyright (c) 2011 LRUnit, LLC All Rights Reserved.
// Author : Kim McKinley
//
// This product includes software developed by :
//  LRUnit, LLC
//  Sqlite (http://www.sqlite.org). SQLite is in the Public Domain
//
// This software is under the terms of the MIT License :
/*
  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files (the "Software"), to deal
  in the Software without restriction, including without limitation the rights
  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  copies of the Software, and to permit persons to whom the Software is
  furnished to do so, subject to the following conditions:

  The above copyright notice and this permission notice shall be included in
  all copies or substantial portions of the Software.

  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  THE SOFTWARE.
*/

#if !defined(CDN_DB_Sqlite_h)
#define CDN_DB_Sqlite_h

/// Sqlite rows are mapped by key/value pairs [ie:(column name)/(sql data)] for processing
#define SqlData std::map< std::string, std::string >

using namespace xmlw;

namespace cdn {
  namespace db {

/// SQLite Error Codes - got these from sqlite.h
#define LAST_ERROR_CODE 26
static const char *SQLiteCodes[] =
{
  "SQLITE_OK (0) Successful",
  "SQLITE_ERROR (1) SQL error or missing database",
  "SQLITE_INTERNAL (2) Internal logic error in SQLite",
  "SQLITE_PERM (3) Access permission denied",
  "SQLITE_ABORT (4) Callback routine requested an abort",
  "SQLITE_BUSY (5) The database file is locked",
  "SQLITE_LOCKED (6) A table in the database is locked",
  "SQLITE_NOMEM (7) A malloc() failed",
  "SQLITE_READONLY (8) Attempt to write a readonly database",
  "SQLITE_INTERRUPT (9) Operation terminated by sqlite3_interrupt()",
  "SQLITE_IOERR (10) Some kind of disk I/O error occurred",
  "SQLITE_CORRUPT (11) The database disk image is malformed",
  "SQLITE_NOTFOUND (12) Unknown opcode in sqlite3_file_control()",
  "SQLITE_FULL (13) Insertion failed because database is full",
  "SQLITE_CANTOPEN (14) Unable to open the database file",
  "SQLITE_PROTOCOL (15) Database lock protocol error",
  "SQLITE_EMPTY (16) Database is empty",
  "SQLITE_SCHEMA (17) The database schema changed",
  "SQLITE_TOOBIG (18) String or BLOB exceeds size limit",
  "SQLITE_CONSTRAINT (19) Abort due to constraint violation",
  "SQLITE_MISMATCH (20) Data type mismatch",
  "SQLITE_MISUSE (21) Library used incorrectly",
  "SQLITE_NOLFS (22) Uses OS features not supported on host",
  "SQLITE_AUTH (23) Authorization denied",
  "SQLITE_FORMAT (24) Auxiliary database format error",
  "SQLITE_RANGE (25) 2nd parameter to sqlite3_bind out of range",
  "SQLITE_NOTADB (26) File opened that is not a database file",
  "" // Stopper technically not needed - but good habit
};


/// %Sqlite implementation of Sql data storage with HTML and XML retrieval
/**
  An instance of this class is created for each request from the client(s).
  The class is used not only to execute any SQLite command but to builds HTML/XML output
  for queries as well.\n
  Note that it is safe for the same database to be accessed by multiple threads as long as 
  the threads do not share the same 'sqlite3 *db' pointer. See http://www.sqlite.org/threadsafe.html \n
  If you notice, each instance of class Sqlite has it's own 'sqlite3 *db' pointer.\n
  The caller/creator must insure that only a single thread uses an instance of the
  class at a time. The safest method is to have each thread construct, use, destruct
  it's own instance of this class.\n
*/
class Sqlite
{
public:

  std::string dbname; ///< Name of database
  sqlite3 *db;        ///< sqlite3 db struct

  /// Create the instance to access a SQLite database
  Sqlite() : db(NULL) {}

  /// Destuctor insures database is closed
  virtual ~Sqlite() { Close(); }

/// Open a database
bool Open( const std::string &DBName, bool create = false )
{
  char *zErrMsg = 0;
  int rc;

  // Already open - so we are good
  if ( db ) return true;

  // Remember the database name
  dbname = DBName;

  // Not allowed to specify drive or parent directories in database name
  if ( dbname.find_first_of(":") != std::string::npos || 
          dbname.find("..") != std::string::npos )
  {
    std::stringstream msg; msg << "Invalid database name " << dbname;
    std::cout << std::endl << "!!! " << msg.str() << std::endl << std::endl;
    throw std::exception(msg.str().c_str());
  }

  // Do we allow creation of databases?
  if ( toupper(gConfig["DEFAULT"]["ALLOW_CREATE_DB"][0]) != 'Y' )
  {
    // Insure the database exists, if not throw an exception
    std::fstream filestr(dbname.c_str(), std::fstream::in);
    if ( filestr.fail() )
    {
      std::stringstream msg; msg << "Database " << dbname << " not found.";
      std::cout << std::endl << "!!! " << msg.str() << std::endl << std::endl;
      throw std::exception(msg.str().c_str());
    }
  }

  // Open the db file
  rc = sqlite3_open(DBName.c_str(), &db);

  // Open failed
  if( rc != SQLITE_OK )
  {
    std::stringstream msg; msg << rcReport(rc) << " - Runtime message: " << (zErrMsg ? zErrMsg : "None");
    std::cout << std::endl << "!!! " << msg.str() << std::endl << std::endl;
    if (zErrMsg) sqlite3_free(zErrMsg);
    db = NULL;
    throw std::exception(msg.str().c_str());
  }
  else // success
  {
    std::cout << "Open Sqlite database " << dbname << " successful" << std::endl;
  }

  // return result
  return ( db ? true : false );
}

/// Return SQLite's Version
std::string SqliteVersion() { return std::string(SQLITE_VERSION); }

/// Check to see if a database is already open
bool IsOpen() { return ( db ? true : false ); }

/// Close the database
void Close( )
{
  if ( !IsOpen() ) return;

  sqlite3_close(db);
  db = NULL;
  std::cout << "Close Sqlite database " << dbname << " successful" << std::endl;
}

/// Report of error codes
const char *rcReport(int rc)
{
  if ( rc >= 0 && rc <= LAST_ERROR_CODE )
    return SQLiteCodes[rc];
  return SQLiteCodes[LAST_ERROR_CODE + 1];
}


/// Given a sql statement - execute it
/**
  This function is called to perform SQL commands that do not return data, such as
  Create, Drop, etc.  The response will indicate success or failure.
*/
void ExecuteSql(const std::string &command)
{
  char *zErrMsg = 0;
  int rc;

  // Execute the SQLite command
  rc = sqlite3_exec(db, command.c_str(), NULL, 0, &zErrMsg);

  // Command failed  - so build the error response, send to console,
  //  and throw exception to be caught by caller
  if ( rc != SQLITE_OK )
  {
    std::stringstream msg; msg << rcReport(rc) << " - Runtime message: " << (zErrMsg ? zErrMsg : "None");
    std::cout << std::endl << "!!! " << msg.str() << std::endl << std::endl;
    if (zErrMsg) sqlite3_free(zErrMsg);
    throw std::exception(msg.str().c_str());
  }
}

/// All records retrieved for a SQL select statement are loaded into a vector of maps of field pairs
int LoadRecordsIntoXmlStream(const std::string &sql, XmlStream &xml)
{
  char *zErrMsg = 0;
  int rc;
  std::stringstream stream;
  SqlData::iterator it;

  // Prepare the SQL statement
  sqlite3_stmt *stmt;
  rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, 0 );

  // Failed to prepare statement?
  if ( rc != SQLITE_OK ) 
  { 
    std::stringstream msg; msg << rcReport(rc) << " - Runtime message: " << (zErrMsg ? zErrMsg : "None");
    std::cout << std::endl << "!!! " << msg.str() << std::endl << std::endl;
    if (zErrMsg) sqlite3_free(zErrMsg);
    throw std::exception(msg.str().c_str());
 }

  xml << tag("Rows");

  // Load up the data framed XML style
  std::string fld, val;
  int rows = 1;
  while ( sqlite3_step(stmt) == SQLITE_ROW )
  {
    // Indicate the record/row number as an attribute
    xml << tag("Row") << attr("rowid") << rows++;
    // Build the XML field and value 
    for (int i = 0; i < sqlite3_column_count(stmt); i++ )
    {
      fld = sqlite3_column_name(stmt,i);
      val = sqlite3_column_text(stmt,i) ? (reinterpret_cast<const char*>(sqlite3_column_text(stmt,i))) : "";
      xml << tag(fld.c_str()) << chardata() << val << endtag();
    }
    xml << endtag();
  }

  xml << endtag();

  sqlite3_finalize(stmt);
  return rows-1;
}

/// All records retrieved for a SQL select statement are loaded into a vector of maps of field pairs
int LoadRecordsIntoHtmlStream(const std::string &sql, std::stringstream &output)
{
  char *zErrMsg = 0;
  int rc;
  std::stringstream stream;
  SqlData::iterator it;

  sqlite3_stmt *stmt;
  rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, 0 );

  // Failed to prepare statement?
  if ( rc != SQLITE_OK ) 
  { 
    output << "<div class='SQLiteError'>\n";
    std::stringstream msg; msg << rcReport(rc) << " - Runtime message: " << (zErrMsg ? zErrMsg : "None");
    std::cout << std::endl << "!!! " << msg.str() << std::endl << std::endl;
    if (zErrMsg) sqlite3_free(zErrMsg);
    output << "</div>\n";
    throw std::exception(msg.str().c_str());
  }

  output << "<table border=\"1\">\n";

  // Load up the data framed HTML Table style
  int rows = 1;
  std::string fld, val;
  while ( sqlite3_step(stmt) == SQLITE_ROW )
  {
    // If the first row then build the headers on the table
    if ( rows++ == 1 )
    {
      output << "<tr>";
      for (int i = 0; i < sqlite3_column_count(stmt); i++ )
      {
        fld = sqlite3_column_name(stmt,i);
        output << "<th>" << fld << "</th>";
      }
      output << "</tr>\n";
    }

    output << "<tr>";
    for (int i = 0; i < sqlite3_column_count(stmt); i++ )
    {
      val = sqlite3_column_text(stmt,i) ? (reinterpret_cast<const char*>(sqlite3_column_text(stmt,i))) : "";
      output << "<td>" << val << "</td>";
    }
    output << "</tr>\n";
  }
  output << "</table>\n";

  sqlite3_finalize(stmt);
  return rows-1;
}

/// Retrieves the column names for a table/view and returns them in a vector
/**
  Just in case we need know what the column names are of a table/view the user is requesting.\n
  (Not currently used).
*/
void GetTableColumnNames(const std::string &tablename, std::vector<std::string> &colnames)
{
  char *zErrMsg = 0;
  int rc;
  std::stringstream stream;
  SqlData::iterator it;

  stream << "PRAGMA table_info('" << tablename << "');";

  colnames.clear();

  sqlite3_stmt *stmt;
  rc = sqlite3_prepare_v2(db, stream.str().c_str(), -1, &stmt, 0 );

  // Failed to prepare statement?
  if ( rc != SQLITE_OK )
  {
    std::stringstream msg; msg << rcReport(rc) << " - Runtime message: " << (zErrMsg ? zErrMsg : "None");
    std::cout << std::endl << "!!! " << msg.str() << std::endl << std::endl;
    if (zErrMsg) sqlite3_free(zErrMsg);
    throw std::exception(msg.str().c_str());
  }

  std::string fld;
  SqlData record;
  // Should be a row with meta data for(about) every column
  while ( sqlite3_step(stmt) == SQLITE_ROW )
  {
    // Look at the meta data for the columns
    for (int i = 0; i < sqlite3_column_count(stmt); i++ )
    {
      fld = sqlite3_column_name(stmt,i);
      // If is the 'name' of the column - that's what we want so push onto the vector
      if ( fld == "name" )
      {
        colnames.push_back( sqlite3_column_text(stmt,i) ? (reinterpret_cast<const char*>(sqlite3_column_text(stmt,i))) : "" );
        break;
      }
    }
  }

  // Done getting the names
  sqlite3_finalize(stmt);
}

}; // class Sqlite

} // namespace db
} // namespace cdn

#endif
